create   database photofpt

use photofpt

create  table [admin]
(
id int identity(100,1) primary key,
username nvarchar(100),
[password]nvarchar (100),
)

insert into admin(username,[password]) values ('admin','123456');

create procedure adminselect
as
begin
select * from   [admin]
end

go
create  procedure adminlogin
@user varchar(50),
@pass varchar(50)
as
begin
select username from [admin] where username = @user and [password]= @pass
end
go

create table useraccount
(
id int identity(200,1) primary key,
username nvarchar(100),
[password] nvarchar(100),
Name nvarchar(1000),
Age int,
Email nvarchar(1000),
Country nvarchar(100),
ImageUpload nvarchar(max) null
)
set identity_insert useraccount on
go
insert into useraccount(id,username,[password],Name,Age,Email,Country,ImageUpload) values(1,'An','sa','Nguyen Van An',18,'An123@yahoo.com.vn','VietNam','');
go
set identity_insert useraccount off
go
insert into useraccount(username,[password],Name,Age,Email,Country,ImageUpload) values('Quan','dah','Hoang Thanh Quan', 25,'Quan10@gmail.com','Cambodia','');

go



  
 create procedure pdinsert

@username nvarchar(50),
@password nvarchar(50),
@Name nvarchar(50),
@Age int,
@Email nvarchar(50),
@Country nvarchar(50),
@ImageUpload nvarchar(max)
as
begin
insert into useraccount(username,[password],Name,Age,Email,Country,ImageUpload) values(@username,@password,@Name,@Age,@Email,@Country,@ImageUpload)
end
 go

 create procedure pdselect
as
begin
select * from useraccount
end
go
   
  create  procedure pddelete
@id int
as
begin
delete from useraccount where id = @id
end
go 

create  procedure pdupdate
@id int,
@username nvarchar(50),
@password nvarchar(50),
@Name nvarchar(50),
@Age int,
@Email nvarchar(50),
@Country nvarchar(50),
@ImageUpload nvarchar(max)
as
begin
update useraccount set username = @username,[password] = @password,Name=@name,Age=@age,Email=@Email,Country=@country,ImageUpload=@ImageUpload where id=@id
end
go

create procedure userlogin
@user varchar(50),
@pass varchar(50)
as
begin
select username from useraccount where username = @user and [password]= @pass
end
go

create table news
(
ID int identity(300,1) primary key, 
Title nvarchar(1000),
News nvarchar(1000),
Photo nvarchar(max)
)
/*Procedure News */
	/*View*/
	Create procedure deNews
		as
		begin
		select * from news
		end
		go
	exec deNews
		go
	/*Add new*/
	Create procedure deAdd 
		@Title nvarchar(1000),
		@News nvarchar(1000),
		@Photo nvarchar(max)
		as
		begin
		insert into news(Title, News, Photo) values(@Title,@News,@Photo)
		end
		go
	exec deAdd 'New','Nini','Er'
	exec deNews
	/*Update*/
	Create procedure deUpdate
		@ID int,
		@Title nvarchar(1000),
		@News nvarchar(1000),
		@Photo nvarchar(max)
		as
		begin
		update news set Title=@Title,News=@News,Photo=@Photo where ID=@ID
		end
		go
	exec deUpdate 300,'Ni','Hinh moi','Hinh Anh'
	exec deNews
	/*Delete*/
	Create procedure deDelete
		@ID int
		as
		begin
		delete from news where ID=@ID
		end
		go
	exec deDelete 300
	exec deNews		
/*-----------------------------------------------------------------------------------------*/

create table FAQ
(
ID int identity(400,1) primary key,
Question nvarchar(max),
Answer nvarchar(max)
)

Create procedure getFAQ
As
	Begin
	select * from FAQ
	End
go

exec getFAQ

Create procedure addFAQ
(
@ID as int =0,
@Question as nvarchar(max),
@Answer as nvarchar(max)
)
As
	Begin
		Insert into FAQ(Question,Answer) values(@Question,@Answer)
	End
Go
set identity_insert FAQ on
exec addFAQ 401,'chim la gi?','la mot loai gia cam'
set identity_insert FAQ off

Create procedure delFAQ

@ID as int

As
	Begin
		delete from FAQ where ID = @ID
	End
Go
exec delFAQ 401

Create procedure UpdateFAQ
@ID as int =0,
@Question as nvarchar(max),
@Answer as nvarchar(max)
As
Begin 

	update FAQ set Question=@Question,Answer=@Answer where ID=@ID

End
go

exec UpdateFAQ 400,'chim cu gay la gi?', 'la loai chim cu biet gay'

create table goodphoto
(
ID int identity(500,1) primary key,
Names nvarchar(100),
Photo nvarchar(max)

)

create table Blog
(
ID int identity(600,1) primary key,
Topic nvarchar(max),
photo  nvarchar(max)
)
set identity_insert Blog on
go
insert into Blog(ID, Topic, photo) values(600,'Flower','a');
go
set identity_insert Blog off
go
insert into Blog(Topic, photo) values('Animal', 's');
insert into Blog(Topic, photo) values('Seasons','');
select * from Blog

create procedure demoBlog
    as
    begin
    select * from Blog
    end
    go
exec demoBlog

create procedure InsertBlog
   @Topic nvarchar(max),
   @photo nvarchar(max)
   as
   begin
     insert into Blog(Topic, photo) values(@Topic, @photo)
   end
   go
   exec InsertBlog 'nana','Es'
   exec demoBlog

create procedure UpdateBlog
   @ID int,
   @Topic nvarchar(max),
   @photo nvarchar(max)
   as
   begin
      update Blog set Topic=@Topic, photo=@photo where ID=@ID
   end
   go
   exec UpdateBlog 601,'hinh anh moi','Ad'
   exec demoBlog
   
create procedure DeleteBlog
   @ID int
   as
   begin
     delete from Blog where ID=@ID
   end
   go
   exec DeleteBlog 600
   exec demoBlog